<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*,java.io.*,data.*"%>
			<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
			<p>Assist a student X in producing his class schedule</p>
				<%
					Map<Integer, Set<Integer>> conflicts = null;
					Map<Integer, String> classes = null;
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");
						
						// Get current quarter
						InputStream in = application.getResourceAsStream("settings.properties");
						Properties p = new Properties();
						p.load(in);
						String quarter = p.getProperty("quarter");
						int year = Integer.parseInt(p.getProperty("year"));
						
						// Sections in this quarter, lazy initialization
						SectionList sectionsInThisQuarter = null;
	
						String action = request.getParameter("action");
						// Check if an query is requested
						if (action != null && action.equals("submit")) {
							// Begin transaction
							conn.setAutoCommit(false);
							
							// Lazy initialization.
							if (sectionsInThisQuarter == null) {
								sectionsInThisQuarter = new SectionList(quarter, year);
								classes = sectionsInThisQuarter.getClasses();
							}
							
							// Get sections taken by the student
							PreparedStatement pstmt = conn
									.prepareStatement("SELECT c.class_id, weekday, start_time, end_time FROM enroll e, section s, class c, meeting m WHERE e.student_id = ? AND e.section_id = s.section_id " + 
											"AND s.class_id = c.class_id AND m.section_id = s.section_id AND c.quarter = ? and c.year = ?");
							pstmt.setString(1, request.getParameter("student_id"));
							pstmt.setString(2, quarter);
							pstmt.setInt(3, year);
							
							ResultSet rsMeeting = pstmt.executeQuery();
							CachedRowSet cachedMeeting = new CachedRowSetImpl();
							cachedMeeting.populate(rsMeeting);
							
							Map<Integer, List<Meeting>> studentMeetings = new HashMap<Integer, List<Meeting>>();
							
							while(cachedMeeting.next()) {
								int classId = cachedMeeting.getInt("class_id");
								String[] weekdays = cachedMeeting.getString("weekday").split(" ");
								String startTime = cachedMeeting.getString("start_time");
								String endTime = cachedMeeting.getString("end_time");
								if (!studentMeetings.containsKey(classId)) {
									studentMeetings.put(classId, new LinkedList<Meeting>());
								}
								for (String weekday : weekdays) {
									studentMeetings.get(classId).add(new Meeting(weekday, startTime, endTime));
								}
							}
							
							conflicts = new TreeMap<Integer, Set<Integer>>();
							Map<Integer, Map<Integer, List<Meeting>>> allMeetings = sectionsInThisQuarter.getMeetings();
							for (int classId : allMeetings.keySet()) {					
								Map<Integer, List<Meeting>> classMeetings = allMeetings.get(classId);
								boolean classConflict = true;
								conflicts.put(classId, new TreeSet<Integer>());
								for (int sectionId : classMeetings.keySet()) {
									boolean sectionConflict = false;
									for (Meeting m1 : classMeetings.get(sectionId)) {
										for (int studentClassId : studentMeetings.keySet()) {
											for (Meeting m2 : studentMeetings.get(studentClassId)) {
												if (studentClassId != classId && m1.overlap(m2)) {
													sectionConflict = true;
													conflicts.get(classId).add(studentClassId);
												}
											}
										}
									}
									if (!sectionConflict) {
										classConflict = false;
										break;
									}
								}
								if (!classConflict) {
									conflicts.remove(classId);
								}
							}
							
							conn.setAutoCommit(true);
						}
						
						// Create the statement
						Statement statement = conn.createStatement();
						
						// Get all students enrolled in current quartesr
						PreparedStatement pstmt = conn.prepareStatement("SELECT s.student_id, first_name, middle_name, last_name, ssn FROM " +
							"student s, student_enrollment e WHERE s.student_id = e.student_id AND e.quarter = ? AND e.year = ?");
						pstmt.setString(1, quarter);
						pstmt.setInt(2, year);
						ResultSet rsStudent = pstmt.executeQuery();
						CachedRowSet cachedStudent = new CachedRowSetImpl();
						cachedStudent.populate(rsStudent);
				%>
				<table border="1">
					<tr>
						<form action="report2a.jsp" method="POST">
							<input type="hidden" value="submit" name="action">
							<th>
								<select name="student_id" style="width:200px">
								<%
								String student_id = request.getParameter("student_id");
								while (cachedStudent.next()) {
								%>
									<option value=<%= cachedStudent.getString("student_id") %> 
										<%
											if (student_id != null && student_id.equals(cachedStudent.getString("student_id")))
												out.println("selected=selected");
										%>>
										<%= cachedStudent.getString("student_id") %> 
										<%= cachedStudent.getString("first_name") %> 
										<%= cachedStudent.getString("middle_name") %> 
										<%= cachedStudent.getString("last_name") %> 
										SSN:<%= cachedStudent.getString("ssn") %> 
									</option>
								<% 	
								}
								%>
								</select>
							</th>
							<th><input type="submit" value="submit"></th>
						</form>
					</tr>
				</table>
				<%
					if (conflicts != null && conflicts.size() == 0) {
						%><p>No conflicts found.</p><%
					}
					else if (conflicts != null && classes != null){
				%>
					<table border="1">
						<tr>
							<th>Classes you cannot take</th>
							<th>Conflicts with classes you take</th>
						</tr>
						<tr>
						<%
							for (int classId : conflicts.keySet()) {
						%>
							<td><%= classes.get(classId) %></td>
							<td>
							<%
								for (int conflictId : conflicts.get(classId)) {
							%>
								<%= classes.get(conflictId) %><br>
							<%
								}
							%>
							</td>
						<%		
							}
						%>
						</tr>
					</table>
				<%
					}
				%>
				<%
					} catch (SQLException e) {
						System.out.println(e.getMessage());
					} catch (Exception e) {
						System.out.println(e.getMessage());
					}
				%>
			</td>
		</tr>
	</table>
</body>
</html>